REALSQLdatabase Class
The REALSQLdatabase class provides access to the REALSQLdatabase data source, a.k.a., database engine or database back-end. It is based on SQLite, which is described at http://www.SQLite.org. Both the Standard and Professional versions of REALbasic fully support the REALSQLdatabase database engine.
More information available in parent classes: Database:Object
Plug-ins for other database engines are supported only in the Professional version of REALbasic.
Use the REALSQLdatabase class to open or create REALSQLdatabases programmatically. A REALSQLdatabase can also be created or opened with the Project . Add . Database submenu and designed with a graphical user interface.
The REALSQLdatabase is not supported on Mac OS "classic" builds. Use the REALDatabase engine that was introduced in version 5.5 of REALbasic.
Notes
The REALSQLdatabase supports a subset of SQL/92 and SQL/99, including queries that involve self-joins, aggregate functions, and more. For the set of features the new REALSQLdatabase engine supports, its syntax is fully SQL compliant. It returns SQLite error codes.
A call to SQLSelect returns a dynamic RecordSet; you can move forward, backward, or jump to the beginning or end as much as you like.
The REALSQLdatabase engine supports transactions, both for schema changes and for data changes. A transaction is started automatically when you make any change to the database and is ended by calling either the Commit or Rollback methods of the Database class.
Result Codes
The REALSQLdatabase engine sets the Database class's Error flag after each operation and returns values in the ErrorCode and ErrorMessage properties. When the Error flag is False, the ErrorCode is "0" and the ErrorMessage is "Not an error". If the Error flag is True, the following codes and messages are returned in ErrorMessage
REAL SQL Database returns SQLite error codes as follows.
| Error code | Error Message |
| 0 | Not an error |
| 1 | SQL logic error or missing database |
| 2 | Internal SQLite implementation flaw |
| 3 | Access permission denied |
| 4 | Callback requested query abort |
| 5 | Database is locked |
| 6 | Database table is locked |
| 7 | Out of memory |
| 8 | Attempt to write a readonly database |
| 9 | Interrupted |
| 10 | Disk I/O error |
| 11 | Database disk image is malformed |
| 12 | Table or record not found |
| 13 | Database is full |
| 14 | Unable to open database file |
| 15 | Database locking protocol failure |
| 16 | Table contains no data |
| 17 | Database schema has changed |
| 18 | Too much data for one table row |
| 19 | Constraint failed |
| 20 | Datatype mismatch |
| 21 | Library routine called out of sequence |
| 22 | Kernel lacks large file support |
| 23 | Authorization denied |
| 24 | Auxiliary database format error |
| 25 | Bind or column index out of range |
| 26 | File is encrypted or is not a database |
Primary Keys
All REAL SQL Database tables have an Integer Primary Key column. If you don't explicitly such a column, one will be created for you. You can refer to the INTEGER PRIMARY KEY column using the "rowid" keyword. But, if you don't explicitly define your own INTEGER PRIMARY KEY column, you won't get the 'rowid' column unless you ask for it in queries, such as in the statement:
Data Types
The following table contains information about the data types used by REAL SQL Database.
| FieldType | Description |
| Binary | Stores code, images, and hexadecimal data. Consult the documentation of your data source for information on the maximum size of a Binary field. |
| Blob | Stores a binary object. The REAL SQL Database supports blobs of up to any size. Furthermore, a blob can be stored in a column of any declared data affinity. If you are using another data source, check the documentation of your data source. Blob data can be inserted into a REAL SQL Database using the BlobColumn method of the DatabaseRecord class. |
| Boolean | Stores the values of TRUE or FALSE. |
| Date | Stores year, month, and day values of a date in the format YYYY-MM-DD. The year value is four digits; the month and day values are two digits. |
| Double | Stores double-precision floating-point numbers. |
| Float | Stores floating-point numeric values with a precision that you specify, i.e., FLOAT (5). |
| Integer | A numeric data type with no fractional part. The maximum number of digits is implementation-specific. The REAL database supports 4-byte integers, which provide a range of ±2,000,000,000. If you are using another data source, check the documentation of your data source. |
| SmallInt | A numeric data type with no fractional part. The maximum number of digits is implementation-specific, but is usually less than or equal to INTEGER. The REAL database supports 2-byte smallints, which allow you to store values in the range of ±32,767. If you are using another data source, check the documentation of your data source. |
| Time | Stores hour, minute, and second values of a time in the format HH:MM:SS. The hours and minutes are two digits. The seconds values is also two digits, may include a optional fractional part, e.g., 09:55:25.248. The default length of the fractional part is zero. |
| TimeStamp | Stores both date and time information in the format YYYY-MM-DD HH:MM:SS. The lengths of the components of a TimeStamp are the same as for Time and Date, except that the default length of the fractional part of the time component is six digits rather than zero. If a TimeStamp values has no fractional component, then its length is 19 digits If it has a fractional component, its length is 20 digits, plus the length of the fractional component. |
| VarChar | Stores alphabetic data, in which the number of characters vary from record to record, but you don't want to pad the unused characters with blanks. REAL SQL Database converts text to UTF-8 text encoding. SQLite expects text to be in that encoding. |
Creating a REALSQLdatabase
You can create a REALSQLdatabase via the IDE by choosing Project . Add . Database . New REAL SQL Database. REALbasic will then present a standard save-file dialog box in which you can name the database and specify the directory in which it will be stored.
When you click OK, a REALSQLdatabase will be added to your project, with the name you entered in the dialog. You can double-click the item to add tables, fields, and indexes. When data have been entered, you can use a table viewer to view the data. For more information, see the chapter on Databases in the Users Guide.
Examples
The following example creates a new REALSQLdatabase:
Dim f as FolderItem
f= New FolderItem("mydb")
db= New REALSQLdatabase
db.databaseFile=f
If db.CreateDatabaseFile then
//proceed with database operations...
else
MsgBox "Database not created"
end if
The following example opens an existing REALSQLdatabase.
Dim db as REALSQLdatabase
db= New REALSQLdatabase
dbFile = GetFolderItem("Pubs")
db.DatabaseFile=dbFile
If db.Connect() then
//proceed with database operations here..
else
Beep
MsgBox "The database couldn't be opened."
end if
The following example adds a record to a table.
Dim db as REALSQLdatabase
db= New REALSQLdatabase
Dim rs As New RecordSet
dbFile= New FolderItem("Employees")
db.databaseFile=dbFile
if db.error then
MsgBox db.errormessage
else
db.sqlexecute ("Insert into Employees (Name,Job,YearJoined) Values " _
+"('Dr.Strangelove','Advisor',1962)")
If db.error then
MsgBox db.errormessage
else
db.Commit
end if
end if
See Also
Database, DatabaseRecord, RecordSet classes.